package com.seafood.repository;

import com.seafood.entity.LocationAddress;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.List;
import java.util.Optional;

/**
 * 地址位置Repository
 */
@Repository
public interface LocationAddressRepository extends JpaRepository<LocationAddress, Long> {
    
    /**
     * 根据业务ID和地址类型查找地址
     */
    List<LocationAddress> findByBusinessIdAndAddressTypeAndIsActiveTrue(
            Long businessId, LocationAddress.AddressType addressType);
    
    /**
     * 根据业务ID查找所有有效地址
     */
    List<LocationAddress> findByBusinessIdAndIsActiveTrueOrderByIsDefaultDescCreatedAtDesc(Long businessId);
    
    /**
     * 根据地址类型查找地址
     */
    Page<LocationAddress> findByAddressTypeAndIsActiveTrue(
            LocationAddress.AddressType addressType, Pageable pageable);
    
    /**
     * 根据省市区查找地址
     */
    List<LocationAddress> findByProvinceAndCityAndDistrictAndIsActiveTrue(
            String province, String city, String district);
    
    /**
     * 查找默认地址
     */
    Optional<LocationAddress> findByBusinessIdAndAddressTypeAndIsDefaultTrueAndIsActiveTrue(
            Long businessId, LocationAddress.AddressType addressType);
    
    /**
     * 根据坐标范围查找地址（矩形范围）
     */
    @Query("SELECT la FROM LocationAddress la WHERE la.isActive = true " +
           "AND la.latitude BETWEEN :minLat AND :maxLat " +
           "AND la.longitude BETWEEN :minLon AND :maxLon")
    List<LocationAddress> findByCoordinateRange(
            @Param("minLat") BigDecimal minLat, @Param("maxLat") BigDecimal maxLat,
            @Param("minLon") BigDecimal minLon, @Param("maxLon") BigDecimal maxLon);
    
    /**
     * 根据地址关键词搜索
     */
    @Query("SELECT la FROM LocationAddress la WHERE la.isActive = true " +
           "AND (la.addressName LIKE %:keyword% OR la.detailedAddress LIKE %:keyword% " +
           "OR la.province LIKE %:keyword% OR la.city LIKE %:keyword% " +
           "OR la.district LIKE %:keyword%)")
    Page<LocationAddress> searchByKeyword(@Param("keyword") String keyword, Pageable pageable);
    
    /**
     * 查找附近的地址（简单版本，实际应使用空间索引）
     */
    @Query("SELECT la FROM LocationAddress la WHERE la.isActive = true " +
           "AND la.addressType = :addressType " +
           "AND ABS(la.latitude - :latitude) <= :latRange " +
           "AND ABS(la.longitude - :longitude) <= :lonRange")
    List<LocationAddress> findNearbyAddresses(
            @Param("latitude") BigDecimal latitude, @Param("longitude") BigDecimal longitude,
            @Param("latRange") BigDecimal latRange, @Param("lonRange") BigDecimal lonRange,
            @Param("addressType") LocationAddress.AddressType addressType);
    
    /**
     * 统计各地址类型数量
     */
    @Query("SELECT la.addressType, COUNT(la) FROM LocationAddress la " +
           "WHERE la.isActive = true GROUP BY la.addressType")
    List<Object[]> countByAddressType();
    
    /**
     * 统计各省份地址数量
     */
    @Query("SELECT la.province, COUNT(la) FROM LocationAddress la " +
           "WHERE la.isActive = true AND la.province IS NOT NULL GROUP BY la.province")
    List<Object[]> countByProvince();
    
    /**
     * 统计各城市地址数量
     */
    @Query("SELECT CONCAT(la.province, '-', la.city), COUNT(la) FROM LocationAddress la " +
           "WHERE la.isActive = true AND la.province IS NOT NULL AND la.city IS NOT NULL " +
           "GROUP BY la.province, la.city")
    List<Object[]> countByCity();
    
    /**
     * 查找重复坐标的地址
     */
    @Query("SELECT la FROM LocationAddress la WHERE la.isActive = true " +
           "AND la.latitude = :latitude AND la.longitude = :longitude " +
           "AND la.id != :excludeId")
    List<LocationAddress> findDuplicateCoordinates(
            @Param("latitude") BigDecimal latitude, @Param("longitude") BigDecimal longitude,
            @Param("excludeId") Long excludeId);
    
    /**
     * 根据联系人查找地址
     */
    List<LocationAddress> findByContactNameContainingAndIsActiveTrueOrderByCreatedAtDesc(String contactName);
    
    /**
     * 根据联系电话查找地址
     */
    Optional<LocationAddress> findByContactPhoneAndIsActiveTrue(String contactPhone);
}